package net.kesumu.yushabuserver.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import net.kesumu.yushabuserver.dao.IYushabuMessageDao;
import net.kesumu.yushabuserver.entity.YushabuMessage;
import net.kesumu.yushabuserver.utils.DataSource;

public class YushabuMessageDaoImpl implements IYushabuMessageDao{

    public YushabuMessageDaoImpl() {
    }

    @Override
    public YushabuMessage storeMessage(YushabuMessage originalMessage) {
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String insertSQL = 
                "INSERT INTO " + DataSource.dbName + ".message "
                + "(content, sendTime, state, userId, sessionId, friendUserId) "
                + "VALUES"
                + "(" 
                + "'" + originalMessage.getContent() + "', " 
                + "'" + originalMessage.getSendTime().toString() + "', " 
                + "'" + originalMessage.getState() + "', " 
                + "'" + originalMessage.getUserId() + "', " 
                + "'" + originalMessage.getSessionId() + "', " 
                + "'" + originalMessage.getFriendUserId() + "'" 
                + ")";
//        System.out.println(insertSQL);
        try {
            PreparedStatement insertStatement_Message = conn.prepareStatement(insertSQL
                                                                , Statement.RETURN_GENERATED_KEYS);
            int affectedRows = insertStatement_Message.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Creating user failed, no rows affected.");
            }
            try (ResultSet generatedKeys = insertStatement_Message.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    //get the messageId just inserted
                    originalMessage.setMessageId(generatedKeys.getInt(1));
//                    System.out.println(originalMessage.getMessageId());
                }
                else {
                    throw new SQLException("Creating user failed, no ID obtained.");
                }
            }
        }catch(SQLException e) {
            e.printStackTrace();
            return null;
        }
        return originalMessage;
    }

    @Override
    public ArrayList<YushabuMessage> getUnreceivedMessagesByFriendId(
            String friendId) {
        ArrayList<YushabuMessage> unreceivedMessages = new ArrayList<YushabuMessage>();
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String queryStatement_Message = 
                "SELECT * FROM " + DataSource.dbName + ".message "
                + "WHERE friendUserId = '" + friendId + "'" 
                + "AND state = '" + YushabuMessage.STATE_INITIATED + "'";
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(queryStatement_Message).executeQuery();
            while (resultSet.next()) {
                YushabuMessage unreceivedMessage = new YushabuMessage();
                unreceivedMessage.setMessageId(resultSet.getInt(1));
                unreceivedMessage.setContent(resultSet.getString(2));
                unreceivedMessage.setSendTime(resultSet.getTimestamp(3));
                unreceivedMessage.setState(resultSet.getInt(4));
                unreceivedMessage.setUserId(resultSet.getInt(5));
                unreceivedMessage.setSessionId(resultSet.getInt(6));
                unreceivedMessage.setFriendUserId(resultSet.getInt(7));
                unreceivedMessages.add(unreceivedMessage);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return unreceivedMessages;
    }

    @Override
    public ArrayList<YushabuMessage> get10HistoryMessagesById(
            String friendId, String beforeMessageId) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean updateMessage(YushabuMessage yushabuMessage) {
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String updateSQL = "UPDATE " + DataSource.dbName + ".message m "
                            + "SET m.content = '"+yushabuMessage.getContent()
                            +"', m.sendTime ='"+yushabuMessage.getSendTime().toString()
                            +"', m.state = '"+yushabuMessage.getState()
                            +"', m.userId = '"+yushabuMessage.getUserId()
                            +"', m.sessionId = '"+yushabuMessage.getSessionId()
                            +"', m.friendUserId = '"+yushabuMessage.getFriendUserId()
                            +"' WHERE m.messageId = '"+yushabuMessage.getMessageId()+"'";    
//        System.out.println(updateSQL);
        try {
            conn.prepareStatement(updateSQL).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ds.close();
        }
        return true;
    }

    @Override
    public YushabuMessage getMessageById(int messageId) {
        YushabuMessage yushabuMessage = new YushabuMessage();
        DataSource ds = new DataSource();
        Connection conn = ds.getConnection();
        String selectSQL = "SELECT * FROM " + DataSource.dbName + ".message "
                + "WHERE messageId='" + messageId + "'";
        // System.out.println(selectSQL);
        ResultSet resultSet = null;
        try {
            resultSet = conn.prepareStatement(selectSQL).executeQuery();
            if (resultSet.next()) {
                yushabuMessage.setMessageId(resultSet.getInt(1));
                yushabuMessage.setContent(resultSet.getString(2));
                yushabuMessage.setSendTime(Timestamp.valueOf(resultSet.getString(3)));
                yushabuMessage.setState(resultSet.getInt(4));
                yushabuMessage.setUserId(resultSet.getInt(5));
                yushabuMessage.setSessionId(resultSet.getInt(6));
                yushabuMessage.setFriendUserId(resultSet.getInt(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            ds.close();
        }
        return yushabuMessage;
    }

}
